﻿using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using static Org.BouncyCastle.Crypto.Engines.SM2Engine;
using static System.Net.WebRequestMethods;

namespace ExcelImportApp
{
    public partial class Form1 : Form
    {
        string logTags = "";
        Thread mainThread;
        protected Dictionary<string, string> dicDir = new Dictionary<string, string>();
        public Form1()
        {
            InitializeComponent();
        }

        private void btnOpenFilePath_Click(object sender, EventArgs e)
        {
            label1.Text = "文件路径：";
            string defaultPath = "";
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            //打开的文件夹浏览对话框上的描述  
            dialog.Description = "请选择一个文件夹";
            //是否显示对话框左下角 新建文件夹 按钮，默认为 true  
            dialog.ShowNewFolderButton = false;
            //首次defaultPath为空，按FolderBrowserDialog默认设置（即桌面）选择  
            if (defaultPath != "")
            {
                //设置此次默认目录为上一次选中目录  
                dialog.SelectedPath = defaultPath;
            }
            //按下确定选择的按钮  
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                //记录选中的目录  
                label1.Text = dialog.SelectedPath;

            }

            if (label1.Text != "" && label1.Text != "文件路径：")
            {
                int index = 0;
                DirectoryInfo folder = new DirectoryInfo(dialog.SelectedPath);
                foreach (FileInfo file in folder.GetFiles())
                {
                    index++;
                    richTextBox1.AppendText(file.Name + "\r\n");
                    if (file.Extension.ToLower().Equals(".xlsx"))
                    {
                        if (!dicDir.ContainsKey(file.Name))
                        {
                            dicDir.Add(file.Name, file.FullName);
                        }
                    }
                }

                lbWaite.Text = "待处理（" + index + "）";
            }

        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            if (dicDir.Count > 0)
            {
                mainThread = new Thread(Doing);
                mainThread.IsBackground = true;
                mainThread.Start();

                logTags = DateTime.Now.ToString("yyyyMMddHHmmss");

                WriteLogs("import", logTags + "_" + "开始", "开始处理");

                btnStart.Visible = false;
                btnOpenFilePath.Enabled = false;
            }
            else
            {
                MessageBox.Show("请选择文件路径！");
            }
        }

        private void Doing()
        {
            DataTable dt = new DataTable();

            lbStart.Invoke(new EventHandler(delegate
            {
                lbStart.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }));

            int index = 0;

            foreach (KeyValuePair<string, string> kvp in dicDir)
            {
                index++;

                try
                {
                    lbWaite.Invoke(new EventHandler(delegate
                    {
                        lbWaite.Text = "待处理（" + (dicDir.Count - index) + "）";
                    }));

                    List<string> lines = new List<string>();

                    richTextBox1.Invoke(new EventHandler(delegate
                    {
                        lines = richTextBox1.Lines.ToList();
                        lines.RemoveAt(lines.IndexOf(kvp.Key));
                        richTextBox1.Lines = lines.ToArray();

                    }));


                    dt = ExcelHelper.ExcelToDataTableNoTitle(kvp.Value, 3);

                    if (dt != null && dt.Rows.Count > 0)
                    {
                        lbDoing.Invoke(new EventHandler(delegate
                        {
                            lbDoing.Text = "处理中（" + kvp.Key + "）";
                        }));
                        Random rd = new Random(Guid.NewGuid().GetHashCode());
                        int count = 0;
                        foreach (DataRow dr in dt.Rows)
                        {
                            DataImport(dr, rd, kvp.Value);

                            count++;
                            richTextBox2.Invoke(new EventHandler(delegate
                            {
                                richTextBox2.AppendText(dr[0].ToString() + "\r\n");
                            }));

                            lbCount.Invoke(new EventHandler(delegate
                            {
                                lbCount.Text = "处理进程（" + (count + " / " + dt.Rows.Count) + "）";
                            }));
                        }

                        richTextBox2.Invoke(new EventHandler(delegate
                        {
                            richTextBox2.Text = "";
                        }));

                    }

                    lbFinished.Invoke(new EventHandler(delegate
                    {
                        lbFinished.Text = "处理完成（" + (index) + "）";
                    }));

                    richTextBox3.Invoke(new EventHandler(delegate
                    {
                        richTextBox3.AppendText(kvp.Key + "\r\n");
                    }));

                }
                catch (Exception ee)
                {

                    WriteLogs("import", logTags + "_" + "导入过程中发生异常", "导入过程中发生异常：【" + kvp.Key + "】【" + kvp.Value + "】【" + ee.Message + "】");
                }
            }

            lbEnd.Invoke(new EventHandler(delegate
            {
                lbEnd.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }));

            lbCount.Invoke(new EventHandler(delegate
            {
                lbCount.Text = "全部处理完成！！！";
            }));

            WriteLogs("import", logTags + "_" + "结束", "处理结束");
        }

        private void DataImport(DataRow dr, Random random, string excelFilePath)
        {
            string no = "";
            string _old_name = "";
            bool simple = false;
            int len = 3;

            qn_business model = new qn_business();

            try { no = dr["mobile"].ToString().Trim(); } catch { }

            try
            {
                string _name = "";
                try
                {
                    simple = ((random.Next() % 2) == 0 ? true : false);
                    len = simple ? (((random.Next()) % 2) == 0 ? 3 : 4) : (((random.Next()) % 2) == 0 ? 2 : 3);
                    _name = NameMaker.RandomName(len, simple, _old_name);
                    _old_name = _name;
                }
                catch { _name = "张三"; }

                string _relatives_name = "";
                try
                {
                    simple = (((random.Next()) % 2) == 0 ? true : false);
                    len = simple ? (((random.Next()) % 2) == 0 ? 3 : 4) : (((random.Next()) % 2) == 0 ? 2 : 3);
                    _relatives_name = NameMaker.RandomName(len, simple, _old_name);
                    _old_name = _relatives_name;
                }
                catch { _relatives_name = "李四"; }

                string _friend_name = "";
                try
                {
                    simple = (((random.Next()) % 2) == 0 ? true : false);
                    len = simple ? (((random.Next()) % 2) == 0 ? 3 : 4) : (((random.Next()) % 2) == 0 ? 2 : 3);
                    _friend_name = NameMaker.RandomName(len, simple, _old_name);
                    _old_name = _friend_name;
                }
                catch { _friend_name = "王二"; }

                string _relatives_telphone = "";

                model = new qn_business()
                {
                    address = "",
                    address_details = "",
                    add_time = DateTime.Now,
                    bank_card_number = "",
                    card_front = "",
                    card_reverse = "",
                    company = "",
                    credit = 0,
                    education = "",
                    friend_name = _friend_name,
                    friend_telphone = "",
                    id_number = "",
                    is_accumulation_fund = 0,
                    is_card = 0,
                    is_mortgage = 0,
                    is_read = 1,
                    is_wages = 0,
                    member_id = 0,
                    mobile = no,
                    name = _name,
                    relatives_name = _relatives_name,
                    relatives_telphone = _relatives_telphone,
                    sex = ((random.Next() % 2) == 0 ? "男" : "女")
                };

                Add(model);
            }
            catch (Exception ex)
            {
                WriteLogs("import", logTags + "_" + "导入过程中发生异常", "导入过程中发生异常：【" + no + "】【" + excelFilePath + "】【" + ex.Message + "】");
            }
        }


        public static void WriteLogs(string LogCategory, string LogName, string LogContent, bool onOneFiles = true)
        {
            try
            {
                string path = AppDomain.CurrentDomain.BaseDirectory + @"logs\" + LogCategory + "\\" + DateTime.Now.ToString("yyyy_MM");

                path = path + "\\" + DateTime.Now.ToString("dd");

                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }

                string fileName = onOneFiles ? path + "\\log_" + LogName + ".log" : path + "\\log_" + LogName + "_" + DateTime.Now.ToString("HH") + "_" + DateTime.Now.ToString("mm_ss_ffffff") + ".log";

                string logTags = "********************[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ms") + "]********************\r\n";


                using (FileStream fs = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.Read))
                {
                    using (StreamWriter sw = new StreamWriter(fs))
                    {
                        sw.WriteLine(logTags + LogName + "\r\n" + LogContent + "\r\n");
                        //清空缓冲区
                        sw.Flush();
                        //关闭流
                        sw.Close();
                    }
                }
            }
            catch
            {

            }
        }


        /// <summary>
        /// 增加或更新一条数据
        /// </summary>
        public bool AddUpdate(qn_business model)
        {
            if (Exists(model.mobile))
            {
                return UpdateByMobile(model);
            }
            else
            {
                return Add(model) > 0;
            }
        }

        public bool Exists(string mobile)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from  dt_qn_business");
            strSql.Append(" where mobile=@mobile");
            SqlParameter[] parameters = {
                        new SqlParameter("@mobile", SqlDbType.NVarChar,50)};
            parameters[0].Value = mobile;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateByMobile(qn_business model)
        {
            try
            {
                #region 修改主表数据==========================
                StringBuilder strSql = new StringBuilder();
                StringBuilder str1 = new StringBuilder();
                //利用反射获得属性的所有公共属性
                PropertyInfo[] pros = model.GetType().GetProperties();
                List<SqlParameter> paras = new List<SqlParameter>();
                strSql.Append("update  dt_qn_business set ");
                foreach (PropertyInfo pi in pros)
                {
                    //如果不是主键则追加sql字符串
                    if (!pi.Name.Equals("id"))
                    {
                        //判断属性值是否为空
                        if (pi.GetValue(model, null) != null)
                        {
                            str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                            paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                        }
                    }
                }
                strSql.Append(str1.ToString().Trim(','));
                strSql.Append(" where mobile=@mobile1 ");
                paras.Add(new SqlParameter("@mobile1", model.mobile));
                DbHelperSQL.ExecuteSql(strSql.ToString(), paras.ToArray());

                #endregion               
            }
            catch
            {
                return false;
            }

            return true;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(qn_business model)
        {

            try
            {
                #region 添加主表数据====================
                StringBuilder strSql = new StringBuilder();
                StringBuilder str1 = new StringBuilder();//数据字段
                StringBuilder str2 = new StringBuilder();//数据参数
                                                         //利用反射获得属性的所有公共属性
                PropertyInfo[] pros = model.GetType().GetProperties();
                List<SqlParameter> paras = new List<SqlParameter>();
                strSql.Append("insert into dt_qn_business(");
                foreach (PropertyInfo pi in pros)
                {
                    //如果不是主键则追加sql字符串
                    if (!pi.Name.Equals("id"))
                    {
                        //判断属性值是否为空
                        if (pi.GetValue(model, null) != null)
                        {
                            str1.Append(pi.Name + ",");//拼接字段
                            str2.Append("@" + pi.Name + ",");//声明参数
                            paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                        }
                    }
                }
                strSql.Append(str1.ToString().Trim(','));
                strSql.Append(") values (");
                strSql.Append(str2.ToString().Trim(','));
                strSql.Append(") ");

                return DbHelperSQL.ExecuteSql(strSql.ToString(), paras.ToArray());

                #endregion
            }
            catch
            {
                return 0;
            }
        }
    }
}
